﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using Microsoft.Office.Interop.Excel;
using ePlanet.ArchiveMange.Models;
using ePlanet.ArchiveMange.DataAccess;
using System.Web.Script.Serialization;

namespace Web.Views.DataManage.PLuploads
{
    public class DocumentArchiveProcess
    {
        public string Process(string tempTemplateFilePath, string templateFileName, decimal type)
        {
            ApplicationClass ExcelApp = null;
            Workbook wb = null;
            Worksheet ws = null;
            Worksheet ws2 = null;
            string responseObjectstr = string.Empty;
            JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
            List<Dictionary<string, string>> dicts = new List<Dictionary<string, string>>();
            List<ReturnObject> returnObjects = new List<ReturnObject>();
            bool isTemplateError = false;
            try
            {
                ArchiveMangeModal domainModel = new ArchiveMangeModal();
                AccountArchiveDAL dal = new AccountArchiveDAL();

                object missing = System.Reflection.Missing.Value;
                ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                wb = ExcelApp.Application.Workbooks.Open(tempTemplateFilePath, missing, false, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing);
                ws = (Worksheet)wb.Worksheets.get_Item(1);
                ws2 = (Worksheet)wb.Worksheets.get_Item(2);

                int row = 2;
                object rangeValue2 = null;
                string order = string.Concat(((Range)ws.Cells[row, 1]).Value2);
                while (!string.IsNullOrEmpty(order))
                {
                    #region"Read Excel Range"
                    ArchiveMangeModal.DOCUMENT_ARCHIVERow modelTableRow = domainModel.DOCUMENT_ARCHIVE.NewDOCUMENT_ARCHIVERow();
                    string attachFilePath = string.Empty;
                    modelTableRow.DATAID = string.Concat(Guid.NewGuid());
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 1]).Value2;
                        if (rangeValue2 != null)
                        {
                            bool isRowIdExist = false;
                            int rowId = int.Parse(string.Concat(rangeValue2));
                            foreach (Dictionary<string, string> dict in dicts)
                            {
                                if (dict["rowId"] == rowId.ToString())
                                {
                                    isRowIdExist = true;
                                    break;
                                }
                            }
                            if (isRowIdExist)
                            {
                                ((Range)ws.Cells[row, 1]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange);
                                isTemplateError = true;
                            }
                            else
                            {
                                Dictionary<string, string> dictionary = new Dictionary<string, string>();
                                dictionary["rowId"] = rowId.ToString();
                                dictionary["Id"] = modelTableRow.DATAID;
                                dicts.Add(dictionary);
                            }
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 1]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 1]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 2]).Value2;
                        if (rangeValue2 != null)
                        {
                            attachFilePath = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 2]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 2]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                        isTemplateError = true;
                    }

                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 3]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.ARCHIVENUM = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 3]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 3]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 4]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.TEMPORDERNUM = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 4]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 4]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 5]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.ARCHIVESOURCE = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 5]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 5]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 6]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.FULLFONDSNUM = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 6]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 6]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 7]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.TITLE = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 7]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 7]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 8]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.F_KEEPTIME = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 8]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 8]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 9]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.PUBLISHER = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 9]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception ex)
                    {
                        ((Range)ws.Cells[row, 9]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        throw ex;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 10]).Value2;
                        if (rangeValue2 != null)
                        {
                            double date = double.Parse(string.Concat(rangeValue2));
                            modelTableRow.PUBLISHTIME = DateTime.FromOADate(date);
                        }
                        else
                        {
                            modelTableRow.PUBLISHTIME = new DateTime(1900, 1, 1);
                        }
                    }
                    catch (Exception ex)
                    {
                        ((Range)ws.Cells[row, 10]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        throw ex;
                    }

                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 11]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.REMARK = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 11]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 11]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 12]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.F_CARRIERTYPE = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 12]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 12]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 13]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.F_SECURITYTYPE = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 13]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 13]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 14]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.PAGENUM = decimal.Parse(string.Concat(rangeValue2));
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 14]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 14]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 15]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.DOPLICATEBODYNUM = decimal.Parse(string.Concat(rangeValue2));
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 15]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 15]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 16]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.SUMMARY = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 16]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 16]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 17]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.STORAGETYPE = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 17]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 17]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 18]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.FILENUM = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 18]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 18]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 19]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.BOXNUM = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 19]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 19]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 20]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.EXPANSIONINFO = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 20]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 20]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 21]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.FILEORGID = string.Concat(rangeValue2);
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 21]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 21]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    try
                    {
                        rangeValue2 = ((Range)ws.Cells[row, 22]).Value2;
                        if (rangeValue2 != null)
                        {
                            modelTableRow.MAINBODYNUM = decimal.Parse(string.Concat(rangeValue2));
                        }
                        else
                        {
                            ((Range)ws.Cells[row, 22]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                            isTemplateError = true;
                        }
                    }
                    catch (Exception)
                    {
                        ((Range)ws.Cells[row, 22]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                        isTemplateError = true;
                    }
                    if (!isTemplateError)
                    {
                        domainModel.DOCUMENT_ARCHIVE.AddDOCUMENT_ARCHIVERow(modelTableRow);
                        returnObjects.Add(new ReturnObject { RelatedId = modelTableRow.DATAID, AttachFilePath = attachFilePath });
                    }
                    row++;
                    order = string.Concat(((Range)ws.Cells[row, 1]).Value2);
                    #endregion
                }

                if (!isTemplateError)
                {
                    row = 2;
                    rangeValue2 = null;
                    order = string.Concat(((Range)ws2.Cells[row, 1]).Value2);
                    while (!string.IsNullOrEmpty(order))
                    {
                        #region"Process Excel Sheet2"
                        bool isExist = false;
                        string pId = string.Empty;
                        foreach (Dictionary<string, string> dict in dicts)
                        {
                            if (dict["rowId"] == order)
                            {
                                pId = dict["Id"];
                                isExist = true;
                                break;
                            }
                        }
                        if (!isExist)
                        {
                            ((Range)ws2.Cells[row, 1]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
                            isTemplateError = true;
                            row++;
                            order = string.Concat(((Range)ws2.Cells[row, 1]).Value2);
                            continue;
                        }
                        ArchiveMangeModal.PAGEFILERow pageFileRow = domainModel.PAGEFILE.NewPAGEFILERow();
                        string attachFilePath = string.Empty;
                        pageFileRow.F_ARCHIVEID = pId;
                        pageFileRow.ARCHIVETYPE = type;
                        pageFileRow.DATAID = string.Concat(Guid.NewGuid());
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 2]).Value2;
                            if (rangeValue2 != null)
                            {
                                attachFilePath = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 2]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 2]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 3]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.ARCHIVENUM = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 3]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 3]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 4]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.TEMPORDERNUM = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 4]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 4]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 5]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.ARCHIVESOURCE = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 5]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 5]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 6]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.FULLFONDSNUM = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 6]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 6]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 7]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.TITLE = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 7]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 7]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 8]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.TITLE = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 8]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 8]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 9]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.PUBLISHER = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 9]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 9]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 10]).Value2;
                            if (rangeValue2 != null)
                            {
                                double date = double.Parse(string.Concat(rangeValue2));
                                pageFileRow.PUBLISHTIME = DateTime.FromOADate(date);
                            }
                            else
                            {
                                pageFileRow.PUBLISHTIME = new DateTime(1900, 1, 1);
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 10]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 11]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.FILEORGID = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 11]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 11]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 12]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.F_KEEPTIME = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 12]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 12]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 13]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.F_CARRIERTYPE = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 13]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 13]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 14]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.F_SECURITYTYPE = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 14]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 14]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 15]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.PAGENUM = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 15]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 15]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 16]).Value2;
                            if (rangeValue2 != null)
                            {
                                //MainBodyNum字段已经去掉
                                //pageFileRow.MAINBODYNUM = int.Parse(string.Concat(rangeValue2));
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 16]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 16]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 17]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.HUJIANHAO = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 17]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 17]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 18]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.BOXNUM = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 18]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 18]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 19]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.EXPANSIONINFO = string.Concat(rangeValue2);
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 19]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 19]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        try
                        {
                            rangeValue2 = ((Range)ws2.Cells[row, 20]).Value2;
                            if (rangeValue2 != null)
                            {
                                pageFileRow.ORDERNUM = decimal.Parse(string.Concat(rangeValue2));
                            }
                            else
                            {
                                ((Range)ws2.Cells[row, 20]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                                isTemplateError = true;
                            }
                        }
                        catch (Exception)
                        {
                            ((Range)ws2.Cells[row, 20]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                            isTemplateError = true;
                        }
                        if (!isTemplateError)
                        {
                            domainModel.PAGEFILE.AddPAGEFILERow(pageFileRow);
                            returnObjects.Add(new ReturnObject { RelatedId = pageFileRow.DATAID, AttachFilePath = attachFilePath });
                        }
                        row++;
                        order = string.Concat(((Range)ws2.Cells[row, 1]).Value2);
                        #endregion
                    }
                }
                if (isTemplateError)
                {
                    ResponseObject responseObject = new ResponseObject();
                    responseObject.responseCode = "Template Error";
                    responseObject.responseObject = templateFileName;
                    responseObjectstr = jsSerializer.Serialize(responseObject);
                    return responseObjectstr;
                }
                else
                {
                    ResponseObject responseObj = new ResponseObject();
                    responseObj.responseCode = "Begin UploadFiles";
                    responseObj.responseObject = returnObjects;
                    responseObjectstr = jsSerializer.Serialize(responseObj);
                    domainModel.ACCOUNT_VOUCHER_ARCHIVE.AcceptChanges();
                    domainModel.ACCOUNT_VOUCHER_ARCHIVE.AcceptChanges();
                    foreach (ArchiveMangeModal.DOCUMENT_ARCHIVERow drow in domainModel.DOCUMENT_ARCHIVE.Rows)
                    {
                        drow.SetAdded();
                    }
                    foreach (ArchiveMangeModal.PAGEFILERow prow in domainModel.ACCOUNT_VOUCHER_ARCHIVE.Rows)
                    {
                        prow.SetAdded();
                    }
                    //dal.AddNewSingleWellModel(domainModel);
                    return responseObjectstr;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                wb.Save();
                wb.Close();
                ExcelApp.Workbooks.Close();
                ExcelApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
                ExcelApp = null;
            }
        }
    }
}